﻿ALTER VIEW [dbo].[vw_SearchPatient]
AS
SELECT DISTINCT 
                      p.patISN, p.patID,p.patName, CONVERT(varchar(10), p.patBirthDate, 103) AS patBirthDate, 
                      CONVERT(datetime, CONVERT(varchar(10),clr.clrExaminationDate, 103), 103) AS clrExaminationDate, 
                      CONVERT(varchar(10), clr.clrExaminationDate, 103) AS clrExaminationDateVN,
                      --dbo.ClinicalRecord.clrAppointmentDate, dbo.ClinicalRecord.clrSickChange, 
                      --dbo.ClinicalRecord.clrExaminationTimes, 
                      p.patAddress, p.patSex
                      -- dbo.ClinicalRecord.clrRexaminationTimes, 
                      --dbo.ClinicalRecord.clrRexaminationStatus, dbo.ClinicalRecord.clrPictureStatus,
                    
FROM         ClinicalRecord clr INNER JOIN
             Patients p ON clr.patISN = p.patISN
             
-------------

ALTER TABLE Surgical
ADD surSymbol nvarchar(200) --them column Chu viet tat phau thuat trong he thong

--==================
ALTER TABLE ClinicalPicture
ADD surISN int ,
    clrExaminationTimes int,
    clrRexaminationTimes int,
    clpNo int,
	clpPicName varchar(200),
    patID int
--==============mo bang nay ra ClinicalPicture
--==============sua column clpPrictureDescription thanh column ten la clpPictureDescription
--neu thuc hien lenh sau co loi
sp_rename 'ClinicalPicture.clpPrictureDescription', clpPictureDescription


---
alter PROCEDURE usp_SplitTextList
	@list_text				ntext,
   @Delimiter				varchar(3)
AS

SET NOCOUNT ON

DECLARE @InputLen			integer			-- input text length
DECLARE @TextPos			integer			-- current position within input text
DECLARE @Chunk				nvarchar(4000)	-- chunk within input text
DECLARE @ChunkPos			integer			-- current position within chunk
DECLARE @DelimPos			integer			-- position of delimiter
DECLARE @ChunkLen			integer			-- chunk length
DECLARE @DelimLen			integer			-- delimiter length
DECLARE @ItemBegPos		integer			-- item starting position in text
DECLARE @ItemOrder		integer			-- item order in list
DECLARE @DelimChar		varchar(1)		-- first character of delimiter (simple delimiter)

-- create table to hold list items
-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied
DECLARE @list_items TABLE ( item_order integer, item_begpos integer, item_endpos integer )

-- process list
IF @list_text IS NOT NULL
   BEGIN

	-- initialize
   SET @InputLen = DATALENGTH(@list_text)
   SET @TextPos = 1
	SET @DelimChar = SUBSTRING(@Delimiter, 1, 1)
	SET @DelimLen = DATALENGTH(@Delimiter)
   SET @ItemBegPos = 1
   SET @ItemOrder = 1
   SET @ChunkLen = 1

   -- cycle through input processing chunks
   WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
      BEGIN

      -- get current chunk
      SET @Chunk = SUBSTRING(@list_text, @TextPos, 8000)

      -- setup initial variable values
      SET @ChunkPos = 1
      SET @ChunkLen = DATALENGTH(@Chunk)
      SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)

      -- loop over the chunk, until the last delimiter
      WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0
         BEGIN

			-- see if this is a full delimiter
         IF SUBSTRING(@list_text, (@TextPos + @DelimPos - 1), @DelimLen) = @Delimiter
            BEGIN

				-- insert position
	         INSERT INTO @list_items (item_order, item_begpos, item_endpos)
	         VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)
	         
	         -- adjust positions
	         SET @ItemOrder = @ItemOrder + 1
	         SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen
	         SET @ChunkPos = @DelimPos + @DelimLen

				END
         ELSE
            BEGIN

            -- adjust positions
            SET @ChunkPos = @DelimPos + 1

            END
      
         -- find next delimiter      
         SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)

         END

      -- adjust positions
      SET @TextPos = @TextPos + @ChunkLen

      END

	-- handle last item
   IF @ItemBegPos <= @InputLen
      BEGIN

      -- insert position
      INSERT INTO @list_items (item_order, item_begpos, item_endpos)
      VALUES (@ItemOrder, @ItemBegPos, @InputLen)

      END

	-- delete the bad items
   DELETE FROM @list_items
   WHERE item_endpos < item_begpos

   -- return list items
	SELECT SUBSTRING(@list_text, item_begpos, (item_endpos - item_begpos + 1)) AS item_text
   FROM @list_items
   ORDER BY item_order

   END



RETURN

EXEC usp_SplitTextList
	@list_text = '1234\r\n356\r\n4455\r\n995',
	@Delimiter = '\r\n'


--=====ghi chú================================================================
--=====phải vào bảng Surgical chỉnh lại "Collation" thành "<database default>"
--============================================================================
alter PROC usp_getSurgical
(
	@strSurgical nvarchar(4000)
)
AS
BEGIN
   --DECLARE @tableSurgical TABLE (surName nvarchar(200))
   CREATE TABLE #tableSurgical (surName nvarchar(200))

	INSERT INTO #tableSurgical EXEC usp_SplitTextList @strSurgical,';'

   --INSERT @tableSurgical EXEC usp_SplitTextList @strSurgical,';'
   SELECT  sur.* FROM Surgical sur INNER join  #tableSurgical t ON sur.surName COLLATE DATABASE_DEFAULT = ltrim(RTRIM(t.surName )) COLLATE DATABASE_DEFAULT
   DROP TABLE  #tableSurgical
END

EXEC usp_getSurgical '1234;dsf;'

SELECT * FROM sysobjects s 

---================================================
---========Them column mexOrder <STT> vao SamplePrescription
ALTER TABLE SamplePrescription
ADD mexOrder int -- nham sap xep STT

---================================================
---========Them column mexOrder <STT> vao MedicineExport
ALTER TABLE MedicineExport
ADD mexOrder int -- nham sap xep STT

---================================================
---========Cap nhat lai store, cho them STT vao
ALTER PROCEDURE [dbo].[sp_SamplePrescription_Insert]
@mimID varchar(20), -- ma thuoc nhap vao
@samSickName nvarchar(100), -- ma chuan doan
@mexMedicineName nvarchar(59), -- ten thuoc
@mexUsage nvarchar(100), -- cach dùng : uống
@mexQuantityPerUnit int, -- số lượng
@mexProductionPerUnit tinyint, --đơn vị lúc nhap thuoc vo kho 
@mexTakePerDay int, -- cữ , ngày bao nhieu lan
@mexQuantityPerDay nvarchar(50), -- cữ : ngày bao nhieu vien
@mexUsagePerDay nvarchar(50), -- cữ : sáng , trưa , chiều
@mexUsageUnit nvarchar(50), --đơn vị : viên ,nhát, muỗng
@mexNote nvarchar(100), -- ghi chú
@mexOrder int -- STT cua toa thuoc
AS
Begin
Insert SamplePrescription (mimID,  mexMedicineName, mexUsage, mexQuantityPerUnit, mexProductionPerUnit, mexTakePerDay, mexQuantityPerDay, mexUsagePerDay, mexUsageUnit, mexNote , samSickName,mexOrder)
Values (@mimID,  @mexMedicineName, @mexUsage, @mexQuantityPerUnit, @mexProductionPerUnit, @mexTakePerDay, @mexQuantityPerDay, @mexUsagePerDay, @mexUsageUnit, @mexNote , @samSickName,@mexOrder)
End


---================================================
---========Cap nhat lai store, cho them STT vao
ALTER PROCEDURE [dbo].[sp_MedicineExport_Insert] 
@mexMedicineName  nvarchar(200), 
@clrISN int, 
@mimID nvarchar(20), 
@mexQuantityPerUnit  int, 
@mexTakePerDay int, 
@mexProductionPerUnit tinyint ,
@mexQuantityPerDay nvarchar(50) , 
@mexUsagePerDay nvarchar(20) ,
--@mexMediceOrderSource nvarchar(100),
@mexNote nvarchar(100),
@mexUsage nvarchar(100),
@mexUsageUnit nvarchar(50),
@invMedicinePayment  money,
@mexTotalQuantity  int, 
@mexTotalUsageDay int,
@mexOrder int --them so thu vao trong toa thuoc

 AS
Declare @mesDate datetime , @mesISN int
Declare @mesQuantityInStock int , @mexStatus tinyint --@mimID varchar(20) ,
--Select @mexStatus = mexStatus from MedicineExport Where clrISN = @clrISN -- thaoht edit
Set  @mesDate  = (Select max(mesDate) from MedicineStock where  mimID  =   @mimID   )
Select  @mesISN = mesISN , @mesQuantityInStock =  mesQuantityInStock   from MedicineStock  Where  mimID  =   @mimID    and  mesDate = @mesDate

--IF @mexStatus <> 2 -- thaoht edit
--Begin -- thaoht edit
	If @mesISN <> 0 and  @mesISN is not null
	Begin
		Insert MedicineExport( mesISN, mexMedicineName, mexQuantityPerUnit,  mexTakePerDay, mexQuantityPerDay, mexUsagePerDay,  mexDateExport , mexStatus , clrISN, mexNote , mexUsage ,mexUsageUnit  , mexProductionPerUnit,
		mexTotalQuantity,mexTotalUsageDay,mexOrder)
		Values (@mesISN, @mexMedicineName, @mexQuantityPerUnit,    @mexTakePerDay, @mexQuantityPerDay, @mexUsagePerDay,  getdate(), 1  , @clrISN, @mexNote , @mexUsage ,@mexUsageUnit  , @mexProductionPerUnit,
        @mexTotalQuantity,@mexTotalUsageDay,@mexOrder)
	End
	Else
		Insert MedicineExport( mesISN, mexMedicineName, mexQuantityPerUnit,  mexTakePerDay, mexQuantityPerDay, mexUsagePerDay,  mexDateExport , mexStatus , clrISN, mexNote , mexUsage ,mexUsageUnit  , mexProductionPerUnit,
		mexTotalQuantity,mexTotalUsageDay,mexOrder)
		Values (@mesISN, @mexMedicineName, @mexQuantityPerUnit,    @mexTakePerDay, @mexQuantityPerDay, @mexUsagePerDay,  getdate(), 0  , @clrISN, @mexNote , @mexUsage ,@mexUsageUnit  , @mexProductionPerUnit,
		@mexTotalQuantity,@mexTotalUsageDay,@mexOrder)
--thaoht edit begin
--End
--Else
--IF @clrISN = 0
--Begin
--
----Select   @mesISN = mesISN  from MedicineStock  Where   mimID  =   @mimID   
----Select @mesQuantityInStock =  mesQuantityInStock  from MedicineStock Where mimID = @mimID and mesDate  = @mesDate  
--
--	Insert  MedicineStock(mimID, mesMedicineName, mesQuantityInStock, mesDate, mesProductionPerUnit) 
--	values(@mimID, @mexMedicineName, @mesQuantityInStock - @mexTotalQuantity , getdate() , @mexProductionPerUnit )
--
--	Set @mesISN = (select IDENT_CURRENT('MedicineStock'))
--	Insert MedicineExport( mesISN, mexMedicineName, mexQuantityPerUnit,  mexTakePerDay, mexQuantityPerDay, mexUsagePerDay,  mexDateExport , mexStatus , clrISN, mexNote , mexUsage ,mexUsageUnit  , mexProductionPerUnit,
--	mexTotalQuantity,mexTotalUsageDay)
--	Values (@mesISN, @mexMedicineName, @mexQuantityPerUnit,    @mexTakePerDay, @mexQuantityPerDay, @mexUsagePerDay,  getdate(), 3  , @clrISN, @mexNote , @mexUsage ,@mexUsageUnit  , @mexProductionPerUnit,
--	@mexTotalQuantity,@mexTotalUsageDay)
--
--	Insert Invoices(clrISN, invMedicinePayment, invPaymentStatus) values(@clrISN, @invMedicinePayment , 3)
--
--End

--------------=============
ALTER VIEW [dbo].[vw_AllPrescription]
AS
SELECT DISTINCT 
                     /*  TOP 20 PERCENT */ dbo.MedicineExport.mexOrder,dbo.MedicineExport.mexMedicineName, dbo.MedicineExport.mexTakePerDay, dbo.MedicineExport.mexISN, 
                      dbo.MedicineExport.mesISN, dbo.MedicineExport.mexUsage, dbo.MedicineExport.mexQuantityPerUnit, dbo.MedicineExport.mexProductionPerUnit, 
                      dbo.MedicineExport.mexDateExport, dbo.MedicineExport.mexUsageUnit, dbo.MedicineExport.mexNote, dbo.MedicineExport.mexStatus, 
                      dbo.MedicineExport.mexQuantityPerDay, dbo.MedicineExport.mexUsagePerDay, dbo.MedicineExport.mexMediceOrderSource, 
                      dbo.MedicineStock.mimID, 
                       ----edit here by thaoht 20090531
                     -- dbo.PathologicalOutList.polISN, 
                     -- dbo.Employees.empISN, dbo.Employees.empID, dbo.Employees.empName, 
                     -- dbo.ClinicalRecord.clrMedicalAdvice, dbo.ClinicalRecord.clrFollowUpExaminationDate, dbo.ClinicalRecord.clrDoctorPrescription, 
                     -- dbo.ClinicalRecord.clrISN, dbo.ClinicalRecord.clrRexaminationTimes, dbo.ClinicalRecord.clrRexaminationStatus,
                     -- dbo.ClinicalRecord.clrExaminationTimes,dbo.ClinicalRecord.clrPathologicalSigns,dbo.ClinicalRecord.clrExaminationDate, dbo.ClinicalRecord.clrSkill,
                      -- dbo.Patients.patBirthDate, dbo.Patients.patName, dbo.Patients.patISN, dbo.Patients.patID, dbo.Patients.patAge, dbo.Patients.patSex, dbo.Patients.patAddress, 
                      ----loại bỏ phần hóa đơn ra khỏi hệ thống
                     -- dbo.Invoices.invExaminationPayment, dbo.Invoices.invMedicinePayment, dbo.Invoices.invMedicineSkillPayment, 
                     -- dbo.Invoices.invSurgeryPayment, dbo.Invoices.invTotalPayment, dbo.Invoices.invPaymentStatus, dbo.Invoices.invISN, 
                     ----end edit
                     
                      dbo.MedicineExport.mexTotalQuantity, dbo.MedicineExport.mexTotalUsageDay,
                      m.mimMedicineOriginalName,m.mexMoreDetails, dbo.MedicineExport.clrISN --can them ten goc cua thuoc, thong tin thêm của một tên thuốc
FROM        -- dbo.ClinicalRecord INNER JOIN
                     -- dbo.Employees ON dbo.ClinicalRecord.empISN = dbo.Employees.empISN INNER JOIN
                     -- dbo.Patients ON dbo.ClinicalRecord.patISN = dbo.Patients.patISN LEFT OUTER JOIN
                      --dbo.Invoices ON dbo.ClinicalRecord.clrISN = dbo.Invoices.clrISN LEFT OUTER JOIN
                     -- dbo.PathologicalOutList ON dbo.ClinicalRecord.clrISN = dbo.PathologicalOutList.clrISN LEFT OUTER JOIN
                      dbo.MedicineExport /* ON dbo.ClinicalRecord.clrISN = dbo.MedicineExport.clrISN */ LEFT OUTER JOIN
                      dbo.MedicineStock ON dbo.MedicineExport.mesISN = dbo.MedicineStock.mesISN LEFT OUTER JOIN
                      dbo.Medicine m ON dbo.MedicineExport.mexMedicineName=m.medName --edit here de them ten goc cua thuoc
WHERE     (dbo.MedicineExport.mexISN IS NOT NULL)
--ORDER BY dbo.ClinicalRecord.clrExaminationDate

-----======================
-----Thay doi 1 so column trong Patients
ALTER TABLE Patients
ALTER column patTel nvarchar(20)
--======================
ALTER TABLE Patients
ALTER COLUMN patMobile nvarchar(20)

---========================
ALTER PROCEDURE [dbo].[sp_Patients_ClinicalRecord_Insert] 
@patID varchar(10), 
@patName nvarchar(50), 
@patBirthDate datetime, 
@patAge tinyint, 
@patSex nvarchar(10), 
@patAddress nvarchar(100), 
@patTel nvarchar(20), 
@patOccupation nvarchar(200),
@empISN int, 
@clrPulse float, 
@clrTemperature float, 
@clrBloodPressure nvarchar(100), 
@clrWeigh float, 
@clrMedicalHistory nvarchar(300), 
@clrTest nvarchar(300), 
@clrSickNotInList nvarchar(300), 
--@clrPathologicalSigns nvarchar(300), 
@clrExaminationTimes int, 
@clrExaminationDate datetime, 
@clrSkill nvarchar(100),
@clrMedicalAdvice nvarchar(300), 
@clrFollowUpExaminationDate datetime, 
--@clrDoctorPrescription nvarchar(1000),
@clrPicture varchar(200),
@clrSurgery  nvarchar(200),
@patMobile nvarchar(20) ,
@clrAppointmentDate datetime,
@clrSickChange  nvarchar(100),
@clrSickHistory nvarchar(300),
@clrRexaminationTimes int ,
@clrRexaminationStatus tinyint,
@clrType tinyint --edit here 20090606 thêm loại bệnh án 1=TMH; 2=TM
/*
clrRexaminationStatus  0 : ko co tai kham, 1 co tai kham
*/
AS
Begin
	Declare @PatientISN int , @clrISN int
	IF not exists (Select * from  patients where patID= @patID)
	Begin
		Insert Patients(patID, patName, patBirthDate, patAge, patSex, patAddress, patTel, patOccupation, patMobile ) 
		values(@patID, @patName, @patBirthDate, @patAge, @patSex, @patAddress, @patTel, @patOccupation , @patMobile )
		IF exists (Select * from Patients)
		Begin
			select @PatientISN  = (Select IDENT_CURRENT('Patients')) 		
		End
		Else
		Begin
			select @PatientISN  = 1
		End
	End
	Else
	Begin
		Update Patients set  patName = @patName, patBirthDate = @patBirthDate, patAge = @patAge,  patSex= @patSex , patAddress = @patAddress, patTel = @patTel, patOccupation = @patOccupation , patMobile  = @patMobile 
		Where patID = @patID
		set @PatientISN = (Select distinct patISN from Patients where patID = @patID)
	End

	IF exists (Select distinct clrISN from ClinicalRecord where  patISN = @PatientISN and (clrExaminationTimes = @clrExaminationTimes or clrExaminationTimes is null) and ( clrRexaminationTimes = @clrRexaminationTimes or clrRexaminationTimes is null) )	
	Begin
		set @clrISN = ( Select distinct clrISN from ClinicalRecord where  patISN = @PatientISN and (clrExaminationTimes = @clrExaminationTimes or clrExaminationTimes is null ) and ( clrRexaminationTimes = @clrRexaminationTimes or clrRexaminationTimes is null))	
		If @clrSurgery ='' or @clrSurgery is null
		Begin
			Update ClinicalRecord set empISN = @empISN, 
			clrPulse = @clrPulse, clrTemperature = @clrTemperature, clrBloodPressure = @clrBloodPressure, clrWeigh = @clrWeigh, clrMedicalHistory = @clrMedicalHistory, clrTest = @clrTest, clrSickNotInList = @clrSickNotInList, 
			clrExaminationDate = @clrExaminationDate, clrSkill = @clrSkill , clrFollowUpExaminationDate = @clrFollowUpExaminationDate,  clrExaminationTimes = @clrExaminationTimes , clrPicture = @clrPicture , clrMedicalAdvice   =@clrMedicalAdvice
				, clrSickChange =  @clrSickChange , clrSickHistory = @clrSickHistory,--,  clrMedicalAdvice = @clrMedicalAdvice
				clrType =@clrType --edit here 20090606 --thêm loại bệnh án
			Where  clrISN = @clrISN
		End
		Else
		Begin
			Update ClinicalRecord set empISN = @empISN, 
			clrPulse = @clrPulse, clrTemperature = @clrTemperature, clrBloodPressure = @clrBloodPressure, clrWeigh = @clrWeigh, clrMedicalHistory = @clrMedicalHistory, clrTest = @clrTest, clrSickNotInList = @clrSickNotInList, 
			clrExaminationDate = @clrExaminationDate, clrSkill = @clrSkill , clrFollowUpExaminationDate = @clrFollowUpExaminationDate,  clrExaminationTimes = @clrExaminationTimes , clrPicture = @clrPicture , clrSurgery  = @clrSurgery  ,  clrMedicalAdvice   =@clrMedicalAdvice
				, clrAppointmentDate  = @clrAppointmentDate , clrSickChange =  @clrSickChange , clrSickHistory = @clrSickHistory,--,  clrMedicalAdvice = @clrMedicalAdvice
				clrType =@clrType --edit here 20090606 --thêm loại bệnh án
			Where  clrISN = @clrISN
		End
			exec sp_ClinicalPathologicalSign_Update  @clrISN

	End
	Else
	Begin
		If @clrSurgery = '' or @clrSurgery is null	
		Begin 			

			Insert ClinicalRecord (patISN, empISN, clrPulse, clrTemperature, clrBloodPressure, clrWeigh, clrMedicalHistory, clrTest, clrSickNotInList, clrExaminationTimes, clrExaminationDate, clrSkill, clrFollowUpExaminationDate, clrPicture,  clrMedicalAdvice , clrSickChange , clrSickHistory, clrRexaminationTimes , clrRexaminationStatus , clrPictureStatus,clrType ) 
			values (@PatientISN, @empISN, @clrPulse, @clrTemperature, @clrBloodPressure, @clrWeigh, @clrMedicalHistory, @clrTest, @clrSickNotInList, @clrExaminationTimes, @clrExaminationDate, @clrSkill ,  @clrFollowUpExaminationDate, @clrPicture , @clrMedicalAdvice , @clrSickChange , @clrSickHistory, @clrRexaminationTimes, @clrRexaminationStatus,0,@clrType) --edit here 20090606 --thêm clrType loại bệnh án
		End
		Else
		Begin
			Insert ClinicalRecord (patISN, empISN, clrPulse, clrTemperature, clrBloodPressure, clrWeigh, clrMedicalHistory, clrTest, clrSickNotInList, clrExaminationTimes, clrExaminationDate, clrSkill, clrFollowUpExaminationDate, clrPicture, clrSurgery , clrMedicalAdvice , clrAppointmentDate , clrSickChange , clrSickHistory, clrRexaminationTimes , clrRexaminationStatus  , clrPictureStatus,clrType ) 
			values (@PatientISN, @empISN, @clrPulse, @clrTemperature, @clrBloodPressure, @clrWeigh, @clrMedicalHistory, @clrTest, @clrSickNotInList, @clrExaminationTimes, @clrExaminationDate, @clrSkill ,  @clrFollowUpExaminationDate, @clrPicture , @clrSurgery , @clrMedicalAdvice , @clrAppointmentDate , @clrSickChange , @clrSickHistory, @clrRexaminationTimes , @clrRexaminationStatus ,0,@clrType) --edit here 20090606 --thêm clrType loại bệnh án
		
		End
		set @clrISN = (select Ident_current('ClinicalRecord'))
		exec sp_ClinicalPathologicalSign_Update  @clrISN

	End
END
-------=============================
ALTER PROCEDURE [dbo].[sp_ClinicalRecord_Update]
@patISN int,
@patID varchar(10), 
@patName nvarchar(50), 
@patBirthDate datetime, 
@patAge tinyint, 
@patSex nvarchar(10), 
@patAddress nvarchar(100), 
@patTel nvarchar(20), 
@patOccupation nvarchar(200),
@empISN int, 
@clrPulse float, 
@clrTemperature float, 
@clrBloodPressure varchar(50), 
@clrWeigh float, 
@clrMedicalHistory nvarchar(300), 
@clrTest nvarchar(300), 
@clrSickNotInList nvarchar(300), 
--@clrPathologicalSigns nvarchar(300), 
@clrExaminationTimes int, 
--@clrExaminationDate datetime, 
@clrSkill nvarchar(100),
@clrMedicalAdvice nvarchar(300), 
@clrFollowUpExaminationDate datetime, 
--@clrDoctorPrescription nvarchar(300),
@clrSurgery nvarchar(200) ,
@patMobile nvarchar(20),
@clrAppointmentDate datetime,
@clrSickChange  nvarchar(100),
@clrSickHistory nvarchar(300),
@clrRexaminationTimes int,
@clrType tinyint --edit here 20090606 --thêm loại bệnh án 1=TMH, 2 = TM



AS
Begin
	Declare @clrISN int
	Update Patients set  patName = @patName, patBirthDate = @patBirthDate, patAge = @patAge,  patSex= @patSex , patAddress = @patAddress, patTel = @patTel, patOccupation = @patOccupation , patMobile  = @patMobile 
	Where patID = @patID

	Update ClinicalRecord set empISN = @empISN, clrPulse = @clrPulse, clrTemperature = @clrTemperature, clrBloodPressure = @clrBloodPressure, clrWeigh = @clrWeigh, clrMedicalHistory = @clrMedicalHistory, 
	clrTest = @clrTest, clrSickNotInList = @clrSickNotInList, clrSkill  = @clrSkill, clrFollowUpExaminationDate = @clrFollowUpExaminationDate, clrSurgery  = @clrSurgery  , clrMedicalAdvice  = @clrMedicalAdvice , 
	clrAppointmentDate = @clrAppointmentDate , clrSickChange  = @clrSickChange  , clrSickHistory  = @clrSickHistory , clrRexaminationTimes  = @clrRexaminationTimes ,
	clrType = @clrType --edit here 20090606
	Where patISN = @patISN and  clrExaminationTimes = @clrExaminationTimes and clrRexaminationTimes  = @clrRexaminationTimes 
	Select @clrISN = clrISN from ClinicalRecord Where patISN = @patISN and  clrExaminationTimes = @clrExaminationTimes and clrRexaminationTimes  = @clrRexaminationTimes 

--	exec sp_ClinicalPathologicalSign_Update  @clrISN


END
---===========================================
ALTER PROCEDURE [dbo].[sp_Patients_ClinicalRecordNurse_Insert] 
@patID varchar(10), 
@patName nvarchar(50), 
@patBirthDate datetime, 
@patAge tinyint, 
@patSex nvarchar(10), 
@patAddress nvarchar(100), 
@patTel nvarchar(20), 
@patOccupation nvarchar(200),
@empISN int, 
@clrPulse float, 
@clrTemperature float, 
@clrBloodPressure nvarchar(100), 
@clrWeigh float, 
@clrMedicalHistory nvarchar(300), 
@clrTest nvarchar(300), 
@clrSickNotInList nvarchar(300), 
--@clrPathologicalSigns nvarchar(300), 
@clrExaminationTimes int, 
@clrExaminationDate datetime, 
@clrSkill nvarchar(100),
@clrMedicalAdvice nvarchar(300), 
@clrFollowUpExaminationDate datetime, 
--@clrDoctorPrescription nvarchar(1000),
@clrPicture varchar(200),
@clrSurgery  nvarchar(200),
@patMobile nvarchar(20) ,
@clrSickHistory nvarchar(300),
@clrRexaminationTimes int,
@clrRexaminationStatus tinyint,
@clrType tinyint --edit here 20090606 ; 1=TMH ;2 = TM

AS
Begin
	Declare @PatientISN int , @clrISN int
	IF not exists (Select * from  patients where patID= @patID)
	Begin
		Insert Patients(patID, patName, patBirthDate, patAge, patSex, patAddress, patTel, patOccupation, patMobile ) 
		values(@patID, @patName, @patBirthDate, @patAge, @patSex, @patAddress, @patTel, @patOccupation , @patMobile )
		IF exists (Select * from Patients)
		Begin
			select @PatientISN  = (Select IDENT_CURRENT('Patients')) 		
		End
		Else
		Begin
			select @PatientISN  = 1
		End
	End
	Else
	Begin
		Update Patients set  patName = @patName, patBirthDate = @patBirthDate, patAge = @patAge,  patSex= @patSex , patAddress = @patAddress, patTel = @patTel, patOccupation = @patOccupation , patMobile  = @patMobile 
		Where patID = @patID
		set @PatientISN = (Select distinct patISN from Patients where patID = @patID)
	End

	Insert ClinicalRecord (patISN, clrPulse, clrTemperature, clrBloodPressure, clrWeigh, clrMedicalHistory, clrSickNotInList , clrExaminationDate , clrExaminationTimes , clrFollowUpExaminationDate , clrSickHistory, clrRexaminationTimes , clrRexaminationStatus , clrPictureStatus,clrType ) 
		values (@PatientISN, @clrPulse, @clrTemperature, @clrBloodPressure, @clrWeigh, @clrMedicalHistory, @clrSickNotInList , getdate(), @clrExaminationTimes , getdate() , @clrSickHistory, @clrRexaminationTimes , @clrRexaminationStatus ,0,@clrType) --edit here 20090606 ;1=TMH;2=TM


End